import datetime
import itertools
import math
import os
import sys
import time

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.db.CcxDataCenterDb import CcxDataCenter
from src.db.LocalDb import LocalDb
from src.tasks.rankChange.RankComparer import RankComparer
from src.db.FCDB import FCDB
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger
from src.utils import BatchUtils, CommonUtils


class GenRatingChange:
    """
    生成企业评级变动数据
    :return:
    """

    def __init__(self, logger: Logger, dcdb: CcxDataCenter, fcdb: FCDB, localDb: LocalDb):
        self.logger = logger
        self.dcdb = dcdb
        self.fcdb = fcdb
        self.localDb = localDb
        self.ratingOrgQe = {"code": "QE", "name": "QE评级", "type": RankComparer.RANK_TYPE_QE}
        self.ratingOrg = [
            {"code": "80065841", "name": "穆迪", "type": RankComparer.RANK_TYPE_MOODY},
            {"code": "80065840", "name": "惠誉", "type": RankComparer.RANK_TYPE_FITCH},
            {"code": "80058815", "name": "标准普尔", "type": RankComparer.RANK_TYPE_SP},
            {"code": "80002154", "name": "中诚信国际", "type": RankComparer.RANK_TYPE_EXTERNAL},
            {"code": "80002172", "name": "联合资信", "type": RankComparer.RANK_TYPE_EXTERNAL},
            {"code": "80002192", "name": "大公国际", "type": RankComparer.RANK_TYPE_EXTERNAL},
            {"code": "80062388", "name": "上海新世纪", "type": RankComparer.RANK_TYPE_EXTERNAL},
            {"code": "80002101", "name": "远东资信", "type": RankComparer.RANK_TYPE_EXTERNAL},
            {"code": "80000858", "name": "中诚信证评", "type": RankComparer.RANK_TYPE_EXTERNAL},
            {"code": "80129270", "name": "联合评级", "type": RankComparer.RANK_TYPE_EXTERNAL},
            {"code": "80117870", "name": "东方金诚", "type": RankComparer.RANK_TYPE_EXTERNAL},
            {"code": "80002099", "name": "中证鹏元", "type": RankComparer.RANK_TYPE_EXTERNAL}
        ]

    def gen(self):

        # 查询QE评级数据
        self.logger.info(f"查询到Qe评级数据")
        rankDatas = self.__queryQeRankDatas__()
        self.logger.info(f"查询到Qe评级数据 {len(rankDatas)} 条")

        # 生成QE评级变动
        self.__genChangedData__(rankDatas=rankDatas, ratingOrg=self.ratingOrgQe)

        # 查询本地企业库
        self.logger.info(f"查询本地企业数据")
        enterpriseList = self.__queryLocalEnterprise__()
        enterpriseList = {d["comp_code"]: d["credit_code"] for d in enterpriseList}
        self.logger.info(f"查询本地企业数据 {len(enterpriseList)} 条")

        # 循环处理外部评级
        for ratingOrg in self.ratingOrg:

            # 查询公开评级
            self.logger.info(f"查询公开评级数据（{ratingOrg['name']}）")
            publicRankBaseDatas = self.__queryPublicRank__(ratingOrgCode=ratingOrg["code"])
            self.logger.info(f"查询公开评级数据（{ratingOrg['name']}） {len(publicRankBaseDatas)} 条")

            publicRanks = []

            # 转成标准格式
            for r in publicRankBaseDatas:
                if r["COMPCODE"] in enterpriseList.keys():
                    publicRanks.append({
                        "credit_code": enterpriseList[r['COMPCODE']],
                        "rating_date": r['PUBLISHDATE'][:4] + "-" + r['PUBLISHDATE'][4:6] + "-" + r['PUBLISHDATE'][6:],
                        "rank": r['CREDITRATE'],
                        "change_reason": r['FILELINK'],
                        "effective": 1,
                        "rating_org": ratingOrg["name"]
                    })

            # 生成评级变动数据
            self.__genChangedData__(rankDatas=publicRanks, ratingOrg=ratingOrg)

    def __genChangedData__(self, rankDatas, ratingOrg):

        # 生成变更数据
        newChanged = self.__genChanged__(rankDatas, ratingOrg["type"])
        self.logger.info(f"生成 {ratingOrg['name']} 评级变动数据 {len(newChanged)} 条")
        newChanged = {self.__genChangedKey__(d): d for d in newChanged}

        # 抽取现存评级变动数据
        curChanged = self.__queryCurrentChangedData__(ratingOrgName=ratingOrg['name'])
        curChanged = {self.__genChangedKey__(d): d for d in curChanged}
        self.logger.info(f"现有 {ratingOrg['name']} 评级变动数据 {len(curChanged)} 条")

        nSet = set(newChanged.keys())
        cSet = set(curChanged.keys())

        # 新增数据
        insertKeys = nSet - cSet
        if len(insertKeys) > 0:
            insertList = list(newChanged[k] for k in insertKeys)
            BatchUtils.batchInsert(self.__update__, insertList)
            self.logger.info(f"新增 {ratingOrg['name']} {len(insertList)} 条记录")
        else:
            self.logger.info(f"无新增 {ratingOrg['name']} 记录")

        # 更新数据
        updateKeys = nSet & cSet
        if len(updateKeys) > 0:
            updateList = []
            for key in updateKeys:
                if self.__isModified__(newChanged[key], curChanged[key]):
                    updateTarget = newChanged[key]
                    updateTarget["id"] = curChanged[key]["id"]
                    updateList.append(updateTarget)
            if len(updateList) > 0:
                BatchUtils.batchUpdate(self.__update__, updateList)
                self.logger.info(f"更新 {ratingOrg['name']} {len(updateList)} 条记录")
            else:
                self.logger.info(f"无更新 {ratingOrg['name']} 记录")
        else:
            self.logger.info(f"无更新 {ratingOrg['name']} 记录")

        # 删除数据
        deleteKeys = cSet - nSet
        if len(deleteKeys) > 0:
            deleteIds = list(curChanged[k]["id"] for k in deleteKeys)
            BatchUtils.batchUpdate(self.__deleteById__, deleteIds)
            self.logger.info(f"删除 {ratingOrg['name']} {len(deleteIds)}条记录")
        else:
            self.logger.info(f"无删除 {ratingOrg['name']} 记录")

    @staticmethod
    def __genChangedKey__(changedData: dict):
        return f"{changedData['rating_org']}:{changedData['credit_code']}:{changedData['change_date']}"

    @staticmethod
    def __isModified__(d1, d2):
        fields = ["credit_code", "current_rank", "old_rank", "change_rank",
                  "change_type", "change_date", "rating_org", "change_reason"]
        if isinstance(d1, dict) and isinstance(d2, dict):
            for f in fields:
                if f not in d1.keys() or f not in d2.keys():
                    return True
                elif d1[f] != d2[f]:
                    return True
        return False

    def __genChanged__(self, rankDatas: list, rankType):
        changedDatas = []
        # 先排序后分组
        rankDatas = sorted(rankDatas, key=lambda x: (x['credit_code']))
        groups = itertools.groupby(rankDatas, key=lambda x: (x['credit_code']))
        for key, datas in groups:
            # 同组按 评级日期排序
            dataList = sorted(list(datas), key=lambda x: x["rating_date"])
            for i in range(len(dataList)):
                after = dataList[i]

                # 新增 或 失效，仅QE评级有
                if i == 0:
                    if rankType == RankComparer.RANK_TYPE_QE:
                        if after["effective"] != 1:
                            # 失效
                            changedDatas.append(self.__createChangedDataInvalid__(after=after))
                        else:
                            # 首评
                            changedDatas.append(self.__createChangedDataFirst__(after=after))
                    else:
                        # 非QE评级首次评级不算
                        pass
                else:
                    before = dataList[i - 1]
                    # 上一个评级记录无效, 且是QE评级，判定为首评
                    if before["effective"] != 1 and rankType == RankComparer.RANK_TYPE_QE:
                        changedDatas.append(self.__createChangedDataFirst__(after=after))
                    else:
                        changeRank = RankComparer.compare(rank1=after["rank"], rank2=before["rank"], rankType=rankType)
                        # 有变更
                        if changeRank != 0:
                            changedDatas.append(self.__createChangedDataModified__(before=before, after=after, changeRank=changeRank))
        return changedDatas

    def __queryQeRankDatas__(self):
        sql = """
        select credit_code,rating_date,qe_level as `rank`, change_reason, 
        CAST(effective AS UNSIGNED) as effective, 'QE评级' as rating_org
        from qe_result_manage qrm 
        where status = 1 
        order by credit_code,rating_date
        """
        return self.dcdb.Query(sql)

    def __queryPublicRank__(self, ratingOrgCode):
        rateType = ['1', '34', '44', '66', '85', '86']
        sql = """
            SELECT a.COMPCODE, a.RATECOMPCODE, a.PUBLISHDATE, A.CREDITRATE, annt.FILELINK
                FROM tq_bd_creditrtissue a
                LEFT JOIN tq_oa_anntfile annt ON a.pdfid = annt.ANNOUNCEMENTFILEID
            WHERE 
                a.RATETYPE in %s
                AND a.RATECOMPCODE  = %s
        """
        return self.fcdb.Query(sql, (rateType, ratingOrgCode))

    @staticmethod
    def __createChangedDataInvalid__(after):
        """
        生成无效记录
        """
        return {
            "id": None,
            "credit_code": after["credit_code"],
            "current_rank": after["rank"],
            "old_rank": "",
            "change_rank": "",
            "change_type": "失效",
            "change_date": after["rating_date"],
            "rating_org": after["rating_org"],
            "change_reason": after["change_reason"]
        }

    @staticmethod
    def __createChangedDataFirst__(after):
        """
        生成新增记录
        """
        return {
            "id": None,
            "credit_code": after["credit_code"],
            "current_rank": after["rank"],
            "old_rank": "",
            "change_rank": "",
            "change_type": "新增",
            "change_date": after["rating_date"],
            "rating_org": after["rating_org"],
            "change_reason": after["change_reason"]
        }

    @staticmethod
    def __createChangedDataModified__(before, after, changeRank):
        """
        生成变更记录
        """
        return {
            "id": None,
            "credit_code": after["credit_code"],
            "current_rank": after["rank"],
            "old_rank": before["rank"],
            "change_rank": f"{abs(changeRank)}",
            "change_type": ("上调" if changeRank < 0 else "下调"),
            "change_date": after["rating_date"],
            "rating_org": after["rating_org"],
            "change_reason": after["change_reason"]
        }

    def __queryLocalEnterprise__(self):
        sql = "SELECT credit_code, comp_code from ent_enterprise WHERE comp_code IS NOT null"
        return self.localDb.Query(sql)

    def __queryCurrentChangedData__(self, ratingOrgName):
        sql = """
            SELECT * FROM rating_change WHERE rating_org = %s
            """
        return self.localDb.Query(sql, (ratingOrgName,))

    def __deleteData__(self, deleteIds):
        sql = """
            delete from rating_change where id in %s
        """
        return self.localDb.ExecNoneQuery(sql, (deleteIds,))

    def __update__(self, insertList):
        sql = """
            REPLACE INTO rating_change
            (id,credit_code, current_rank, old_rank, change_rank, 
                change_type, change_date, rating_org, change_reason)
            VALUES(%(id)s,%(credit_code)s, %(current_rank)s, %(old_rank)s, %(change_rank)s, 
                %(change_type)s, %(change_date)s, %(rating_org)s,%(change_reason)s);
            """
        return self.localDb.ExecMany(sql, insertList)

    def __deleteById__(self, deleteIds):
        sql = """
                DELETE FROM rating_change WHERE id IN %s;
            """
        return self.localDb.ExecNoneQuery(sql, (deleteIds,))


# 主函数
def main():
    logger = Logger()
    dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)
    fcdb = FCDB(logSql=False, autocommit=True)
    localDb = LocalDb(logSql=False, autocommit=True)

    cleanScreen()
    t = time.perf_counter()

    logger.info("生成企业评级变动数据 开始")

    gen = GenRatingChange(logger=logger, dcdb=dataCenterDb, fcdb=fcdb, localDb=localDb)
    gen.gen()

    cast = (time.perf_counter() - t)
    m = math.floor(cast / 60)
    s = cast - m * 60
    logger.info('总耗时: %d分%.3f秒' % (m, s))
    logger.info("生成企业评级变动数据 结束")


# 程序入口
if __name__ == "__main__":
    main()
